/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.DAO;

import com.aptech.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Hoang Quy Hong An
 */
public class userDAO {

    private Connection myCon;
    private PreparedStatement ps;
    private ResultSet rs;
    private String strCommand = "";

    public boolean addUser(User user) {
        boolean flag = false;
        try {
            strCommand = "INSERT INTO tbl_Users (userName ,userEmail ,userPassword ,userAddress ,userPhone ,userTypeID, totalMoney ,userStatus) "
                    + "VALUES (? ,? ,? ,? ,? ,?,0 ,1)";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getUserEmail());
            ps.setString(3, user.getUserPassword());
            ps.setString(4, user.getUserAddress());
            ps.setString(5, user.getUserPhone());
            ps.setInt(6, user.getUserTypeID());
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public User login(User user) {
        User objUser = null;
        try {
            strCommand = "SELECT * FROM tbl_Users "
                    + "WHERE userEmail = ? AND userPassword = ? AND userStatus = 1";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, user.getUserEmail());
            ps.setString(2, user.getUserPassword());
            rs = ps.executeQuery();
            while (rs.next()) {
                objUser = new User(rs.getInt("userID"), rs.getString("userName"), rs.getInt("userTypeID"));
            }
            return objUser;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
    }

    public boolean changeOwnProperty(User user) {
        boolean flag = false;
        try {
            System.out.println("user.getUserName(): " + user.getUserName());
            strCommand = "UPDATE tbl_Users "
                    + "SET userName = ?,userAddress = ?,userPhone = ? "
                    + "WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getUserAddress());
            ps.setString(3, user.getUserPhone());
            System.out.println(" user.getUserPhone() :" + user.getUserPhone());
            ps.setInt(4, user.getUserID());
            int i = ps.executeUpdate();
            System.out.println("Update");
            if (i > 0) {
                flag = true;
            }
            System.out.println("UserID: " + user.getUserID());
            System.out.println("I:    " + i);
            System.out.println("flag: " + flag);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public boolean changePassword(User user) {
        boolean flag = false;
        try {
            strCommand = "UPDATE tbl_Users SET userPassword = ? WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, user.getUserPassword());
            ps.setInt(2, user.getUserID());
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public boolean changeUserType(int userID) {
        boolean flag = false;
        try {
            strCommand = "UPDATE tbl_Users SET userTypeID = ? WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, 2);
            ps.setInt(2, userID);
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public User viewOwnProperty(int userID) {
        User user = null;
        try {
            strCommand = "SELECT tbl_Users.userName, tbl_Users.userEmail, tbl_Users.userAddress, tbl_Users.userPhone,tbl_Users.userTypeID , tbl_UserType.userTypeName  "
                    + "FROM tbl_Users INNER JOIN "
                    + "tbl_UserType ON tbl_Users.userTypeID = tbl_UserType.userTypeID WHERE  tbl_Users.userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, userID);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User(userID, rs.getString("userName"),
                        rs.getString("userEmail"), rs.getString("userAddress"),
                        rs.getString("userPhone"), rs.getInt("userTypeID"), rs.getString("userTypeName"));
            }
            return user;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return user;
    }

    public int getUserMoney(int userID) {
        int totalMoney = 0;
        try {
            strCommand = "SELECT totalMoney FROM tbl_Users WHERE userID = ?";

            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, userID);
            rs = ps.executeQuery();
            if (rs.next()) {
                totalMoney = Integer.parseInt(rs.getString("totalMoney"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return totalMoney;
    }

    public int updateMoney(int userID, int money) {
        int userMoney;
        int newMoney;
        try {
            userMoney = getUserMoney(userID);
            newMoney = userMoney + money;
            strCommand = "UPDATE tbl_Users SET totalMoney = ? WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, newMoney + "");
            ps.setInt(2, userID);
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 0;
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
    }

    public boolean checkOldPassword(User user) {
        boolean flag = false;
        try {
            strCommand = "SELECT count(*) as UserCount FROM tbl_Users "
                    + "WHERE userID = ? AND userPassword = ? ";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, user.getUserID());
            ps.setString(2, user.getUserPassword());
            System.out.println(strCommand);
            System.out.println(user.getUserID());
            System.out.println(user.getUserPassword());
            rs = ps.executeQuery();
            if (rs.next()) {
                int userCount = rs.getInt("UserCount");
                if (userCount > 0) {
                    flag = true;
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return flag;
    }

    public User getUserByID (int userID){
        User objUser  = null;
        try {
            strCommand = "SELECT userName ,userEmail ,userAddress ,userPhone FROM tbl_Users WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, userID);
            rs = ps.executeQuery();
            if (rs.next()) {
                objUser = new User();
                objUser.setUserID(userID);
                objUser.setUserName(rs.getString("userName"));
                objUser.setUserEmail(rs.getString("userEmail"));
                objUser.setUserAddress(rs.getString("userAddress"));
                objUser.setUserPhone(rs.getString("userPhone"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return objUser;
    }
}
